{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 独立使用django model（一）\n",
    "# Django Shell\n",
    "# 项目根目录中打开Django Shell : python manage.py shell\n",
    "# import django\n",
    "# django.setup()  # 装载Django\n",
    "\n",
    "# 独立使用django model（二）\n",
    "# python ×××.py or Jupyter Notebook\n",
    "# 将脚本放置在项目根目录下jupyter_notebook文件中比较方便\n",
    "import sys\n",
    "import os\n",
    "import json\n",
    "import requests\n",
    "from django.core.files import File\n",
    "from django.core.files.base import ContentFile\n",
    "# 外部脚本链接django项目\n",
    "# 添加环境变量\n",
    "'''\n",
    "print(os.path.abspath('__file__'))\n",
    "print(os.path.dirname(os.path.abspath('__file__')))\n",
    "print(os.path.dirname(os.path.dirname(os.path.abspath('__file__'))))\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook/__file__\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis\n",
    "'''\n",
    "project = os.path.dirname(os.getcwd())  # get current work directory\n",
    "sys.path.append(project)\n",
    "sys.path.append(os.path.join(project, 'rmis'))\n",
    "# sys.path.append已设置临时环境变量，可以直接调用其中的文件，脚本是外部脚本，只是放在了项目当中而已\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')\n",
    "# 相关数据库配置等都在settings.py文件中\n",
    "# 导入并装载django\n",
    "import django\n",
    "django.setup()\n",
    "# 脚本正文\n",
    "from django.apps import apps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, datetime, timedelta\n",
    "import time\n",
    "from django.db.models import Count, Max, Min\n",
    "from django.forms.models import model_to_dict\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from invoice.models import Sale, GoodInSale\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[datetime.datetime(2017, 1, 1, 0, 0), datetime.datetime(2017, 1, 2, 0, 0), datetime.datetime(2017, 1, 3, 0, 0), datetime.datetime(2017, 1, 4, 0, 0), datetime.datetime(2017, 1, 5, 0, 0)]\n",
      "[{'id': 17619, 'code': '20190415154713177247EMM4UH', 'amount': 0}, {'id': 17617, 'code': '20190415154713167559QW8QNU', 'amount': 0}, {'id': 17620, 'code': '20190415154713181876YU0J7N', 'amount': 0}, {'id': 17618, 'code': '20190415154713172679WM91W5', 'amount': 0}, {'id': 17616, 'code': '20190415154713162739FN1AZZ', 'amount': 0}]\n",
      "[(17619, '20190415154713177247EMM4UH', 0), (17617, '20190415154713167559QW8QNU', 0), (17620, '20190415154713181876YU0J7N', 0), (17618, '20190415154713172679WM91W5', 0), (17616, '20190415154713162739FN1AZZ', 0)]\n",
      "2019-05-18 20:15:51\n",
      "2017-01-01 15:59:25\n",
      "{'_state': <django.db.models.base.ModelState object at 0x1226216a0>, 'id': 17619, 'code': '20190415154713177247EMM4UH', 'fk_store_id': 5, 'fk_clerk_id': 520, 'fk_customer_id': 475, 'created': datetime.datetime(2019, 5, 18, 20, 15, 51), 'modified': datetime.datetime(2019, 4, 15, 15, 47, 13, 177243), 'discount': 0, 'amount': 258, 'interests': 130, 'info': None}\n"
     ]
    }
   ],
   "source": [
    "# days seconds microseconds milliseconds minutes hours  weeks\n",
    "\n",
    "# QuerySet API reference\n",
    "# https://docs.djangoproject.com/en/1.11/ref/models/querysets/#values-list\n",
    "print(\n",
    "    list(\n",
    "        Sale.objects.datetimes('created','day',order='ASC')[:5]  # ASC DESC\n",
    "    )\n",
    ") \n",
    "# [datetime.datetime(2017, 1, 1, 0, 0), datetime.datetime(2017, 1, 2, 0, 0), datetime.datetime(2017, 1, 3, 0, 0), ]\n",
    "\n",
    "print(\n",
    "    list(\n",
    "        Sale.objects.values('id','code','amount')[:5]\n",
    "    )\n",
    ")\n",
    "# [{'id': 17619, 'code': '20190415154713177247EMM4UH', 'amount': 0},]\n",
    "\n",
    "print(\n",
    "    list(\n",
    "        Sale.objects.values_list('id','code','amount')[:5]\n",
    "    )\n",
    ")\n",
    "# [(17619, '20190415154713177247EMM4UH', 0),]\n",
    "\n",
    "print(\n",
    "    Sale.objects.aggregate(Max(\"created\"))['created__max']\n",
    ")\n",
    "\n",
    "print(\n",
    "    Sale.objects.aggregate(Min(\"created\"))['created__min']\n",
    ")\n",
    "\n",
    "print(\n",
    "    Sale.objects.get(code='20190415154713177247EMM4UH').__dict__\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('20190415154713177247EMM4UH', '20190415154713167559QW8QNU', '20190415154713181876YU0J7N'), (258, 499, 747), (130, 250, 375)]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[{'code': '20190415154713177247EMM4UH', 'amount': 0},\n",
       " {'code': '20190415154713167559QW8QNU', 'amount': 0},\n",
       " {'code': '20190415154713181876YU0J7N', 'amount': 0}]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_sample = [(sale.code,sale.amount, sale.interests) for sale in Sale.objects.all()[:3] ]\n",
    "\n",
    "print(\n",
    "    list(\n",
    "        zip(*sales_sample)\n",
    "    )   \n",
    ")\n",
    "# Django ORM用到三个类：Manager、QuerySet、Model\n",
    "# 在init中赋值的计算属性，只有通过遍历queryset才能取到结果\n",
    "for item in Sale.objects.filter(amount__gt=0):\n",
    "    print(item.amount)\n",
    "[item for item in Sale.objects.values('code','amount')[:3] ]\n",
    "# [{'code': '20190415154713177247EMM4UH', 'amount': 0},]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = [model_to_dict(sale) for sale in Sale.objects.all().order_by('created') ]  # .order_by('±field00', '±field01' , ...)\n",
    "created_max = Sale.objects.aggregate(Max(\"created\"))['created__max']\n",
    "created_min = Sale.objects.aggregate(Min(\"created\"))['created__min']\n",
    "\n",
    "\n",
    "df = pd.DataFrame.from_records(sales)\n",
    "df.to_excel(r'./material/data_sample_sales.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = [model_to_dict(sale) for sale in Sale.objects.all().order_by('created') ] \n",
    "sales = zip(*sales)\n",
    "sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
